package com.zxc.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
import java.util.Map.Entry;

import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.SqlConnRunner;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.db.sql.Condition;

public class JdbcDemo {

	public static void main(String[] args) throws SQLException, InterruptedException {
		
//		deleteQueryCidFromtoCount();
//		deleteDataFlightbook();
//		deleteFlightbookExtend();
//		deleteGDSQueryFromtoCount();
//		deleteOpenRouteLog();
//		deletePolicyOptLog();
//		deleteData_tmp_query_f_tjmx();
//		deleteHuilvtemp();
	}
	
	public static void deleteGDSQueryFromtoCount() throws SQLException, InterruptedException {
		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_order?useUnicode=true&characterEncoding=UTF-8";
		String user = "tjfxadmin";
		String pass = "FxCloud!@3456_2017";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 378234032;
		
		int minId = 308158000;
		
		while (minId <= maxId) {
			Entity entity = new Entity("gds_query_fromto_count");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			try {
				if(conn.isClosed()) {
					System.out.println("连接已关闭，重新创建连接");
					conn = ds.getConnection();
				}
				int count = runner.del(conn, entity);
				System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
				minId += 1000;
				Thread.sleep(1000);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deleteQueryCidFromtoCount() throws SQLException, InterruptedException {
		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_order?useUnicode=true&characterEncoding=UTF-8";
		String user = "tjfxadmin";
		String pass = "FxCloud!@3456_2017";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 799122985;
		
		int minId = 797810985;
		
		while (minId <= maxId) {
			Entity entity = new Entity("query_cid_fromto_count");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 1000;
			Thread.sleep(1000);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deleteDataFlightbook() throws SQLException, InterruptedException {
//		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_order?useUnicode=true&characterEncoding=UTF-8";
//		String user = "tjfxadmin";
//		String pass = "FxCloud!@3456_2017";
		
		String url = "jdbc:mysql://122.9.123.86:3306/trip_order?useUnicode=true&characterEncoding=UTF-8";
		String user = "bjlt20240516";
		String pass = "Bjlt20240516Fx!@34";
		
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 6220421;
		
		int minId = 0;
		
		while (minId <= maxId) {
			// DELETE from `flight_book` where id < 27609018 and id <= 34435080;
			Entity entity = new Entity("data_flightbook");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 1000;
			Thread.sleep(1000);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deleteFlightbookExtend() throws SQLException, InterruptedException {
		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_order?useUnicode=true&characterEncoding=UTF-8";
		String user = "tjfxadmin";
		String pass = "FxCloud!@3456_2017";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 39531326;
		
		int minId = 39531001;
		
		while (minId <= maxId) {
			// DELETE from `flight_book` where id < 27609018 and id <= 34435080;
			Entity entity = new Entity("flight_book_extend");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 1000;
			Thread.sleep(100);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deleteData_tmp_query_f_tjmx() throws SQLException, InterruptedException {
//		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_order?useUnicode=true&characterEncoding=UTF-8";
//		String user = "tjfxadmin";
//		String pass = "FxCloud!@3456_2017";
		
		String url = "jdbc:mysql://122.9.123.86:3306/trip_order?useUnicode=true&characterEncoding=UTF-8";
		String user = "bjlt20240516";
		String pass = "Bjlt20240516Fx!@34";
		
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 3005403;
		
		int minId = 0;
		
		while (minId <= maxId) {
			// DELETE from `flight_book` where id < 27609018 and id <= 34435080;
			Entity entity = new Entity("data_tmp_query_f_tjmx");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 10000;
			Thread.sleep(100);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deleteFlightbookHistory() throws SQLException, InterruptedException {
		String url = "jdbc:mysql://114.115.129.180:3306/trip_order_history?useUnicode=true&characterEncoding=UTF-8";
		String user = "tjfxadmin";
		String pass = "FxCloud!@3456_2017";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 35373854;
		
		int minId = 35251081;
		
		while (minId <= maxId) {
			// DELETE from `flight_book` where id < 27609018 and id <= 34435080;
			Entity entity = new Entity("flight_book");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 1000;
			Thread.sleep(1000);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deleteOpenRouteLog() throws SQLException, InterruptedException {
		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_config?useUnicode=true&characterEncoding=UTF-8";
		String user = "tjfxadmin";
		String pass = "FxCloud!@3456_2017";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 70000000;
		
		int minId = 32505000;
		
		while (minId <= maxId) {
			// DELETE from `flight_book` where id < 27609018 and id <= 34435080;
			Entity entity = new Entity("open_route_log");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 1000;
			Thread.sleep(1000);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deleteHuilvtemp() throws SQLException, InterruptedException {
		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_config?useUnicode=true&characterEncoding=UTF-8";
		String user = "tjfxadmin";
		String pass = "FxCloud!@3456_2017";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 2900000;
		
		int minId = 2470000;
		
		while (minId <= maxId) {
			Entity entity = new Entity("huilv_temp");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 1000;
			Thread.sleep(1000);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}
	
	public static void deletePolicyOptLog() throws SQLException, InterruptedException {
		String url = "jdbc:mysql://fxdbo.fengxiangcloud.cn:3306/trip_config?useUnicode=true&characterEncoding=UTF-8";
		String user = "tjfxadmin";
		String pass = "FxCloud!@3456_2017";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Connection conn = ds.getConnection();
		
		int maxId = 53074567;
		
		int minId = 38352733;
		
		while (minId <= maxId) {
			// DELETE from `flight_book` where id < 27609018 and id <= 34435080;
			Entity entity = new Entity("policy_opt_log");
			entity.set("id1", new Condition("id", "<", minId));
			entity.set("id2", new Condition("id", "<=", maxId));
			if(conn.isClosed()) {
				System.out.println("连接已关闭，重新创建连接");
				conn = ds.getConnection();
			}
			int count = runner.del(conn, entity);
			System.out.println("数据区间：【"+minId+","+maxId+"】删除数据行："+count);
			minId += 1000;
			Thread.sleep(1000);
		}
		conn.close();
		System.out.println("删除数据完毕！");
	}

	
	public static void insertAirports(Map<String, Object> map) throws SQLException, InterruptedException {
		String url = "jdbc:mysql://192.168.100.10:3306/test?useUnicode=true&characterEncoding=UTF-8";
		String user = "root";
		String pass = "FxCloud@2023";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Entity record = new Entity("airports");
		for (Entry<String, Object> entry : map.entrySet()) {
			record.set(entry.getKey(), entry.getValue());
		}
		runner.insert(ds.getConnection(), record);
	}
	
	public static void insertAirlines(Map<String, Object> map) throws SQLException, InterruptedException {
		String url = "jdbc:mysql://192.168.100.10:3306/test?useUnicode=true&characterEncoding=UTF-8";
		String user = "root";
		String pass = "FxCloud@2023";
		SimpleDataSource ds = new SimpleDataSource(url, user, pass);
		SqlConnRunner runner = DbUtil.newSqlConnRunner(ds);

		Entity record = new Entity("airlines");
		for (Entry<String, Object> entry : map.entrySet()) {
			record.set(entry.getKey(), entry.getValue());
		}
		runner.insert(ds.getConnection(), record);
	}
}
